| |
163 Search Results Found |
| 1 |
Forum: Data Guard «»
Posted on: Sat, 11 February 2023 11:31 «»
By: Andrey_R
Re: RMAN-06820 on rman backup from standby with OS authentication
…you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
That's a fair point. So I did:
- Install Oracle 19c EE, no PDB, on Windows Server…
|
| 2 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:32 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…is another possible version with tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID…
|
| 3 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:30 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, …
|
| 4 |
Forum: Text & interMedia «»
Posted on: Thu, 10 August 2023 19:05 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…see the following demonstration and answers below.
-- table, initial data, index, additional data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (id NUMBER,
3 document VARCHAR2(…
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 10 March 2023 13:25 «»
By: Barbara Boehmer
Re: Suggestion on count via sql statement
…method:
SCOTT@orcl_12.1.0.2.0> SELECT Name, TRUNC(TEST_Date) AS Test_date, Instrument, COUNT(*) AS count
2 FROM test
3 WHERE Instrument IN ('Down', 'Not available')
4 GROUP BY Name, TRUNC(TEST_Date), Instrument
5 UNION
6 …
|
| 6 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 12:25 «»
By: Barbara Boehmer
Re: difficult task
…tables and such look good, although you may want to make changes later.
For your procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 …
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 09 February 2024 13:17 «»
By: Barbara Boehmer
Re: Xmlattribute not returning tag when the value is null
…am assuming that this is a simplification, and that your data comes from a table with multiple rows and that any value in any column in any row may be null. If this is the case, then after reviewing some of the links from the search link that Michel…
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 20:27 «»
By: Barbara Boehmer
Re: Convert Date formats
…should be comparing dates as dates, not converting them to character strings and comparing those.
Assuming that you have the following table and data
SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
2 /
Table created.
SCOTT@…
|
| 9 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 10:35 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950…
|
| 10 |
Forum: Server Administration «»
Posted on: Tue, 24 May 2022 04:08 «»
By: shamsad.khan@gmail.com
Unable to connect to pluggable database - Oracle Datase 12.2.0.1.0
…
I have installed oracle database 12.2.0.1.0. as container database with orclpdb as my pluggabe database
Logged in sqlplus as connect sys as sysdba
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH;
create user c##test identified by …
|
| 11 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 16:46 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…I am trying to understand what causes an error, I try to narrow it down to the simplest example that I can that reproduces the error and the simplest thing that resolves it. Please see the simplified demonstration below in which the problem is …
|
| 12 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 17 December 2023 20:26 «»
By: Barbara Boehmer
Re: Problem in pivot query
…@OraFerro,
I strongly suspect that you have a minor typing error in your query that is not being posted here. You need to post a copy and paste of an actual complete run as I have done below, including the view creation, including line numbers, …
|
| 13 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 12:28 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…see the changes to line 34 below. It should work with multiple tables or just one. It will just use an IN clause instead of =.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in …
|
| 14 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 04:49 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…is an example for you.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in varchar2,
3 p_table in varchar2)
4 as
5 l_dp_handle …
|
| 15 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 February 2023 15:28 «»
By: Barbara Boehmer
Re: XML parse
…would help to know where your data comes from, how you retrieve it, how it is stored, and what sort of table you want to load it into. In the following I have assumed that it is in a text file on your server. I suggest that you start with the basics and…
|
| 16 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 11:28 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…the translate function, it translates each occurrence of the character in the second parameter to the corresponding character in the third parameter, but just removes others. In the original example, there were only three special characters, so I used …
|
| 17 |
Forum: Text & interMedia «»
Posted on: Tue, 10 January 2023 12:15 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…are a number of issues here. One issue is that Oracle Text does not know what to do with the special characters. Another is that if the text query is not executed and there is no score then it does not know what to do when you have selected that …
|
| 18 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 16 December 2023 17:29 «»
By: Barbara Boehmer
Re: Problem in pivot query
…sum(wtd_year) to sum(wtd_amount)
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12…
|
| 19 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 18 September 2023 11:41 «»
By: Barbara Boehmer
Re: how to get employee in and out time for generated query based on first in and last out
…the following minimal example, dates with only one punch time will have the same date for in and out times. Also, whatever your first column is "M." (machine?) is ignored. It is ordered by the punch_date, which is really just a …
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 07 February 2023 13:48 «»
By: Barbara Boehmer
Re: XML parse
…are two issues here.
You have multiple LEI under different categories, so those can be given different column names, just as the different Id were named FrId and ToId.
You have multiple Rpt under TradData, so you need an additional XMLTABLE. You …
|
| 21 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 06 November 2023 18:02 «»
By: Barbara Boehmer
Re: Recursive subquery factoring
…following applies a function to one column of your existing view.
-- function and query:
create or replace function get_path_id_post_d
(p_path_id_post in varchar2)
return varchar2
as
v_path_id_post_d &…
|
| 22 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 01 October 2023 03:57 «»
By: Barbara Boehmer
Re: Grouping similar strings together using UTL_MATCH Jaro-Winkler
…following produces the same results as your query, but in a slightly different format. It eliminates the possibility of having things that are not closely related in the same group, due to a chain of relations, by comparing collections and only …
|
| 23 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 29 March 2023 19:55 «»
By: Barbara Boehmer
Re: sql query
…
SCOTT@orcl_12.1.0.2.0> COLUMN mod_flag FORMAT A8
SCOTT@orcl_12.1.0.2.0> -- test data you provided:
SCOTT@orcl_12.1.0.2.0> WITH
2 students (id, term, module) AS
3 (SELECT 123, 2023, 2100 FROM DUAL UNION ALL
4 SELECT 123, …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 March 2023 11:24 «»
By: Barbara Boehmer
Re: Identify Groups where 2 or more Clients have bought the same Products - SQL Oracle
…for creating table and data that you should have provided; please do so yourself next time:
CREATE TABLE table1
(group_id VARCHAR2(8),
client_id VARCHAR2(9),
product_id VARCHAR2(10),
prod_qty NUMBER)
/
INSERT ALL
INTO table1 …
|
| 25 |
Forum: Forms «»
Posted on: Sat, 13 August 2022 03:29 «»
By: sasipalarivattom
Re: Files generated on server with sufix
…Hi,
How are you generating the report?
Have you tried generating report in URL
like http://WIN-SERV:9002/reports/rwservlet?server=rep_server1&report=test.rdf&destype=file&desformat=html&userid=scott/ti ger@orcl&desname=c:\rep1.…
|
| 26 |
Forum: Text & interMedia «»
Posted on: Tue, 31 July 2018 21:43 «»
By: Barbara Boehmer
Re: Oracle Text Performance
…is odd, especially since we are using the same version. The slightest mistyping of something can result in an error. Just to rule that out, I have provided a simple test script that you can copy and paste, followed by a run of the same script on my …
|
| 27 |
Forum: Performance Tuning «»
Posted on: Wed, 13 June 2018 23:06 «»
By: Barbara Boehmer
Re: Oracle Text index column when searching multiple tables
…is another method without triggers.
-- version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition …
|
| 28 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 19 March 2018 17:49 «»
By: Barbara Boehmer
Re: return records based on records
…
SCOTT@orcl_12.1.0.2.0> -- test data:
SCOTT@orcl_12.1.0.2.0> with data as
2 (select 'Item111' item1, 'Frozen' cost_type from dual
3 union all
4 select 'Item111' item1, 'Pending' cost_type from dual
5 union all
6 select '…
|
| 29 |
Forum: JDeveloper, Java & XML «»
Posted on: Tue, 20 February 2018 12:47 «»
By: CMcKinstry
Re: Using IDE Database Connections in Java code
…Thanks, that doesn't use the named database connection (testConnection). I'd still have to use the JDBC string along with username and password.
E.g.:
Note:
The login variables have been set to null to secure the application. At this point in the …
|
| 30 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 31 January 2018 18:17 «»
By: Barbara Boehmer
Re: need two token matching query
…requirements seem to be getting more complex in terms of either matching 2 different tokens or matching 2 tokens if there are 2 duplicates in the input string and 2 duplicates of the same token in the fullname and including special characters. It keeps …
|
| 31 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 24 January 2018 16:00 «»
By: Barbara Boehmer
Re: need two token matching query
…
SCOTT@orcl_12.1.0.2.0> create table test_names
2 (id number,
3 id2 number,
4 id3 number,
5 fullname varchar2(500))
6 /
Table created.
SCOTT@orcl_12.1.0.2.0> create or replace function format_input
2 (…
|
| 32 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 22 January 2018 02:40 «»
By: mvrkr44
Re: need two token matching query
…Boehmer ,
Its working fine..But when i add special character ? in the print joins lexer..
it is not working as expected.
insert all
into test_names values (6,'rajesh?','kumar',null,null,null)
select * from dual;
SCOTT@orcl_12.1.0.2.0&…
|
| 33 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 11 December 2017 21:11 «»
By: Barbara Boehmer
Re: need two token matching query
…try the following instead. Also, if you do not need the order by clause, then you can eliminate that.
SCOTT@orcl_12.1.0.2.0> select id, id2, id3
2 from test_names,
3 (select regexp_substr (:inputname, '[^ ]+', 1, rownum) names
…
|
| 34 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 November 2017 23:08 «»
By: Barbara Boehmer
Re: remove duplicate values by date
…-- test data:
SCOTT@orcl_12.1.0.2.0> select * from your_table order by insert_date, element
2 /
ELEMENT STATUS INSERT_DATE
------- ------ -------------------
a UP 11/27/2017 13:00:00
b UP 11/27/2017 13:00:00
c UP…
|
| 35 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 31 July 2017 20:22 «»
By: Barbara Boehmer
Re: Regular Expression : Find and Replace Specific String
…
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (v_ref in out sys_refcursor,
3 v_columns in varchar2)
4 as
5 v_sql varchar2(32767);
6 begin
7 v_sql :=
8 'select ' || v_columns || ', count(ename…
|
| 36 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 14 July 2017 19:19 «»
By: Barbara Boehmer
Re: Insert statement with bind variable and Create statement
…1. Are you doing this from SQL*Plus or from something else like SQL*Developer or Toad? If from SQL*Plus, then it may be do to some setting. Please post the result of SHOW ALL.
2. You cannot split any identifier or value between lines. If you do so…
|
| 37 |
Forum: Server Utilities «»
Posted on: Mon, 26 December 2016 01:16 «»
By: Barbara Boehmer
Re: SQL loader problem when using sequence
…remove dat_c from the control file, as shown below.
SCOTT@orcl_12.1.0.2.0> HOST TYPE test_data.txt
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT
1HHHHH
2MMMMM
3ccccc
4TTTTT
SCOTT@orcl_12.1.0.2.0> HOST TYPE samp.ctl
…
|
| 38 |
Forum: Performance Tuning «»
Posted on: Sat, 03 December 2016 14:26 «»
By: Barbara Boehmer
Re: Materlized View
…is hard to say what is best without understanding why you have the design that you have. It may be that you need to change the design. In general, a regular view is just a stored query that does not store data, whereas a materialized view is more like a…
|
| 39 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 19 September 2016 17:27 «»
By: Barbara Boehmer
Re: PL/SQL Function fails without an exception
…privileges is a likely cause, specifically the lack of privilege to delete from the trace table. If, for example, the function and procedure are created with AUTHID CURRENT_USER and the user executing the procedure lacks the privilege to delete from the …
|
| 40 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 07 September 2016 02:22 «»
By: Barbara Boehmer
Re: Select columns based on first record (Header from flat file)
…
Apparently, your query does care about the order of the columns, because when the order of the columns is changed, as shown below, your query returns no rows.
SCOTT@orcl_12.1.0.2.0> select * from cust
2 /
COL1 COL2 COL3…
|
Blog
Search
Help
Members
Register
Login
Home